/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Modelo.DAL;

import Modelo.Rol;
import Modelo.RolPersonal;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.json.JSONObject;
import org.json.JSONArray;

/**
 *
 * @author andres
 */
public class Rol_DAO {

    public static JSONObject consultarRoles(Rol rol, String limit, String offset, String order, String asc) {
        ConexionBD conex = new ConexionBD();
        JSONObject roles = new JSONObject();

        try {
            String query = "SELECT * FROM rol  ";
            String params = "WHERE (1=1) ";
            if (rol.getNombre() != null && !rol.getNombre().equals("")) {
                params += "and upper(nombre) LIKE '%" + rol.getNombre().toUpperCase() + "%' ";
            }
            if (rol.getCategoria() != null && !rol.getCategoria().equals("")) {
                params += "and categoria LIKE '%" + rol.getCategoria() + "%' ";
            }

            String format = " ";
            if (order != null && !order.equals("")) {
                format += " order by " + order;
                if (asc != null && !asc.equals("") && asc.equals("false")) {
                    format += " desc";
                } else {
                    format += " asc";
                }
            }

            if (limit != null && !limit.equals("")) {
                format += " limit " + limit;
            }
            if (offset != null && !offset.equals("")) {
                format += " offset " + offset;
            }

            System.out.println(query + params + format);
            Statement st = (Statement) conex.session.createStatement();
            ResultSet rs = st.executeQuery(query + params + format);
            int i = 0;
            JSONArray ja = new JSONArray();
            while (rs.next()) {
                JSONObject r = new JSONObject();
                r.put("id", rs.getString(1));
                r.put("nombre", rs.getString(2));
                r.put("categoria", rs.getString(3));
                ja.put(i++, r);
            }

            roles.put("Roles", ja);

            query = "select count(*) from rol ";
            System.out.println(query + params);
            rs = st.executeQuery(query + params);
            if (rs.next()) {
                roles.put("tam", rs.getString(1));
            }
        } catch (Exception e) {
            System.out.println("Error consultando roles: " + e.getMessage());
        } finally {
            if (conex != null) {
                conex.cerrarConexion();
            }
        }
        return roles;
    }

    public static JSONObject consultarRolesPersonal(Rol rol, String cedula, String limit, String offset, String order, String asc) {
        ConexionBD conex = new ConexionBD();
        JSONObject roles = new JSONObject();

        try {
            String query = "SELECT r.* FROM rol r join rol_personal rp "
                    + "on (r.id_rol=rp.id_rol) ";
            String params = "WHERE (1=1) ";
            if (rol.getNombre() != null && !rol.getNombre().equals("")) {
                params += "and upper(r.nombre) LIKE '%" + rol.getNombre().toUpperCase() + "%' ";
            }
            if (rol.getCategoria() != null && !rol.getCategoria().equals("")) {
                params += "and r.categoria LIKE '%" + rol.getCategoria() + "%' ";
            }
            if (cedula != null && !cedula.equals("")) {
                params += "and rp.ci = '" + cedula + "' ";
            }

            String format = " ";
            if (order != null && !order.equals("")) {
                format += " order by r." + order;
                if (asc != null && !asc.equals("") && asc.equals("false")) {
                    format += " desc";
                } else {
                    format += " asc";
                }
            }

            if (limit != null && !limit.equals("")) {
                format += " limit " + limit;
            }
            if (offset != null && !offset.equals("")) {
                format += " offset " + offset;
            }

            System.out.println(query + params + format);
            Statement st = (Statement) conex.session.createStatement();
            ResultSet rs = st.executeQuery(query + params + format);
            int i = 0;
            JSONArray ja = new JSONArray();
            while (rs.next()) {
                JSONObject r = new JSONObject();
                r.put("id", rs.getString(1));
                r.put("nombre", rs.getString(2));
                r.put("categoria", rs.getString(3));
                ja.put(i++, r);
            }

            roles.put("Roles", ja);

            query = "select count(*) FROM rol r join rol_personal rp on (r.id_rol=rp.id_rol) ";
            System.out.println(query + params);
            rs = st.executeQuery(query + params);
            if (rs.next()) {
                roles.put("tam", rs.getString(1));
            }
        } catch (Exception e) {
            System.out.println("Error consultando roles: " + e.getMessage());
        } finally {
            if (conex != null) {
                conex.cerrarConexion();
            }
        }
        return roles;
    }

    public static boolean asignarRolPersonal(RolPersonal rp) {
        ConexionBD conex = new ConexionBD();
        Statement st;
        try {
            st = (Statement) conex.session.createStatement();
            String query = "BEGIN TRANSACTION;"
                    + "DELETE FROM rol_personal WHERE ci='" + rp.getCedula() + "'; ";

            System.out.println(query);
            st.executeUpdate(query);

            String rol = "";
            int i = 0;
            for (i = 0; i < rp.getRoles().length - 1; i++) {
                rol += "('" + rp.getRoles()[i] + "','" + rp.getCedula() + "'), ";
            }
            rol += "(" + rp.getRoles()[i] + ",'" + rp.getCedula() + "'); ";
            query = "INSERT INTO rol_personal VALUES " + rol;

            System.out.println(query);
            int success = st.executeUpdate(query);
            System.out.println("\n\nResult: " + success);
            if (success > 0) {
                st.executeUpdate("COMMIT");
                System.out.println("Commited");
                return true;
            } else {
                System.out.println("rolled back");
                st.executeUpdate("ROLLBACK");
                return false;
            }
        } catch (Exception e) {
            try {
                System.out.println("Error asignando roles: " + e.getMessage());
                st = (Statement) conex.session.createStatement();
                st.executeUpdate("ROLLBACK");
                return false;
            } catch (SQLException ex) {
                System.out.println("Error 2 asignando roles: " + ex.getMessage());
                return false;
            }
        } finally {
            if (conex != null) {
                conex.cerrarConexion();
            }
        }
    }
}
